package zy.dao.base.emp.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.emp.EmpRewardDAO;
import zy.entity.base.emp.T_Base_Emp_Reward;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class EmpRewardDAOImpl extends BaseDaoImpl implements EmpRewardDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_base_emp_reward t");
		sql.append(" JOIN t_base_emp em ON em_code = er_em_code AND em.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = em_shop_code AND sp.companyid = em.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND er_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND er_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("er_em_code"))) {
			sql.append(" AND t.er_em_code=:er_em_code");
		}
		if (StringUtil.isNotEmpty(params.get("er_rw_code"))) {
			sql.append(" AND t.er_rw_code=:er_rw_code");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Base_Emp_Reward> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT er_id,er_em_code,er_rw_code,er_sysdate,er_state,er_remark,t.companyid,em_name,");
		sql.append(" (SELECT rw_name FROM t_sys_reward rw WHERE rw_code = er_rw_code AND rw.companyid = t.companyid LIMIT 1) AS rw_name");
		sql.append(" FROM t_base_emp_reward t");
		sql.append(" JOIN t_base_emp em ON em_code = er_em_code AND em.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = em_shop_code AND sp.companyid = em.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND er_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND er_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("er_em_code"))) {
			sql.append(" AND t.er_em_code=:er_em_code");
		}
		if (StringUtil.isNotEmpty(params.get("er_rw_code"))) {
			sql.append(" AND t.er_rw_code=:er_rw_code");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY er_id DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Emp_Reward.class));
	}
	
	@Override
	public List<T_Base_Emp_Reward> statByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT er_rw_code,rw_name,rw_style,rw_icon,COUNT(1) AS reward_count");
		sql.append(" FROM t_base_emp_reward t");
		sql.append(" JOIN t_sys_reward rw ON rw_code = er_rw_code AND rw.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND er_em_code = :er_em_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY er_rw_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Emp_Reward.class));
	}

	@Override
	public void save(T_Base_Emp_Reward empReward) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_base_emp_reward");
		sql.append(" (er_em_code,er_rw_code,er_sysdate,er_state,er_remark,companyid)");
		sql.append(" VALUES(:er_em_code,:er_rw_code,:er_sysdate,:er_state,:er_remark,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(empReward),holder);
		empReward.setEr_id(holder.getKey().intValue());
	}

	@Override
	public void save(List<T_Base_Emp_Reward> empRewards) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_base_emp_reward");
		sql.append(" (er_em_code,er_rw_code,er_sysdate,er_state,er_remark,companyid)");
		sql.append(" VALUES(:er_em_code,:er_rw_code,:er_sysdate,:er_state,:er_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(empRewards.toArray()));
	}

}
